package cn.topevery.garden.dal.dao.impl;

import cn.topevery.garden.dal.dao.CoreIndicatorsRuleDao;
import cn.topevery.garden.dal.dao.CriterionDefDao;
import cn.topevery.garden.dal.dao.CriterionEvaluationDao;
import cn.topevery.garden.dal.dao.CriterionExpandDao;
import cn.topevery.garden.entity.po.CoreIndicatorsItem;
import cn.topevery.garden.entity.po.CriterionDef;
import cn.topevery.garden.entity.po.CriterionEvaluation;
import cn.topevery.garden.entity.po.CriterionExpand;
import cn.topevery.garden.entity.query.CriterionDefQuery;
import cn.topevery.garden.entity.vo.CriterionDefVO;
import cn.topevery.garden.entity.vo.CriterionTableVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.basis.entity.po.SysUser;
import hw.topevery.basis.runtime.UserRuntime;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.entity.SqlUpdateMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import hw.topevery.framework.entity.Value;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * 综合评价标准定义表
 *
 * @author code-generate
 * @date 2022-01-05
 */
@Repository
public class CriterionDefDaoImpl extends BaseDaoImpl<CriterionDef> implements CriterionDefDao {

    @Autowired
    private CriterionExpandDao criterionExpandDao;
    @Autowired
    private CriterionEvaluationDao criterionEvaluationDao;
    @Autowired
    private CoreIndicatorsRuleDao coreIndicatorsRuleDao;
    @Override
    public <P extends DbCsp> List<CriterionDef> getSearch(P para) {
        return null;
    }

    

    @Override
    public DataPager<CriterionDefVO> listPage(CriterionDefQuery query) {
        String table = "t_criterion_def tcd";

        String columns = "tcd.*";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tcd.c_id desc");
        sqlQueryMate
                .where(true, "tcd.c_db_status", ScriptConditionEnum.Eq, 0)
        ;

        return getDataPager(sqlQueryMate, pageSize, pageIndex, CriterionDefVO.class);
    }

    @Override
    public CriterionDef getEntityByColumn(String key,String value){
        KeyValue<String> map=new KeyValue<>();
        map.key=key;
        map.value=value;
        return getEntityByColumnEqValue(map);
    }

    @Transactional
    @Override
    public void insert(List<CriterionDef> criterionList, Integer userId) {
        CriterionDef criterion;
        for (int i = 0; i < criterionList.size(); i++) {
            criterion = criterionList.get(i);
            criterion.setCriterionId(UUID.randomUUID().toString());
            criterion.setCreateId(userId.toString());
            criterion.setCreateTime(LocalDateTime.now());
            criterion.setDbStatus(false);
            if (i > 0) {//关联关系
                criterion.setRelationId(criterionList.get(i - 1).getCriterionId());
            } else {
                criterion.setRelationId(null);
            }
            if (criterion.getExpandList() != null && !criterion.getExpandList().isEmpty()) {
                criterion.setIsExpand(0);//是否扩展
                criterionExpandDao.insertAll(criterion.getExpandList(), criterion.getCriterionId(), userId);
            } else {
                criterion.setIsExpand(1);
            }
            insert(UserRuntime.getSysUser().userId, criterion);
        }
    }

    @Override
    public List<CriterionDef> getListByPid(String pid) {
        String table = "t_criterion_def tcd\n" +
                "            LEFT JOIN t_sys_dict tsd ON tcd.c_type = tsd.c_dict_code   AND tsd.c_ns_id = 'c73aa972-9bba-43bc-a927-b8d89bd3a264' AND tcd.c_relation_id is null";
        String columns = "tcd.*,\n" +
                "            tsd.c_dict_name AS typeStr";
        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tcd.c_order_num asc");
        sqlQueryMate
                .where(true, "tcd.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(pid), "tcd.c_criterion_pid", ScriptConditionEnum.Eq, pid);
        return selectList(sqlQueryMate, CriterionDef.class);
    }

    @Override
    public List<CriterionDef> getListByPidIgnoreRelation(String pid) {
        return null;
    }

    @Override
    public List<CriterionDef> getRootListByCriterionId(String rootId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * from t_criterion_def where c_db_status = 0 and c_layer = 0  ");
        if (StringUtils.isNotBlank(rootId)) {
            sb.append(" and  c_criterion_id ='" + rootId+"'");
        }
        sb.append(" order by c_order_num asc");
        AtomicReference<List<CriterionDef>> vo = new AtomicReference<>(new ArrayList<>());
        run(sb.toString(), CommandType.Text, dbCommand -> {
            vo.set(dbCommand.executeToList(CriterionDef.class));
        });

        return vo.get();
    }

    @Override
    public List<CriterionDef> getById(String criterionId) {
        List<CriterionDef> criterionList = new ArrayList<>();
        return loopSelect(criterionId, criterionList);
    }
    private List<CriterionDef> loopSelect(String criterionId, List<CriterionDef> criterionList) {
        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
        sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "c_criterion_id", ScriptConditionEnum.Eq, criterionId);
        CriterionDef criterionDef = selectOne(sqlQueryMate, CriterionDef.class);
        List<CriterionExpand> expandList = criterionExpandDao.getListByCriterionId(criterionId);
        criterionDef.setExpandList(expandList);
        List<CriterionEvaluation> evaluationList = criterionEvaluationDao.getListByCriterionId(criterionId);
        criterionDef.setEvaluationList(evaluationList);
        criterionList.add(criterionDef);
        SqlQueryMate sqlQueryMate2 = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
        sqlQueryMate2.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "c_relation_id", ScriptConditionEnum.Eq, criterionId);
        CriterionDef relation = selectOne(sqlQueryMate2, CriterionDef.class);
        if (relation != null) {
            criterionList = loopSelect(relation.getCriterionId(), criterionList);
        }
        return criterionList;
    }
    private void loopDelete(String criterionId, Integer userId) {//删除时要把有关联的标准全部删除
        criterionExpandDao.deleteByCriterionId(criterionId, userId);
        SqlUpdateMate sqlUpdateMate = new SqlUpdateMate("t_criterion_def tcd");
        sqlUpdateMate.setColumnValue(true, "c_db_status",1)
                .where(true, "c_criterion_id", ScriptConditionEnum.Eq, criterionId);
        run(sqlUpdateMate.getSql(), CommandType.Text, dbCommand -> {
            sqlUpdateMate.getParameters().forEach(dbCommand::addParameter);
            dbCommand.executeNonQuery();
        });
        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
        sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(criterionId), "c_relation_id", ScriptConditionEnum.Eq, criterionId);
        CriterionDef criterion = selectOne(sqlQueryMate, CriterionDef.class);
        if (criterion != null) {
            loopDelete(criterion.getCriterionId(), userId);
        }
    }

    @Override
    public void update(List<CriterionDef> criterionList, Integer userId) {
        SysUser user = UserRuntime.getSysUser();
        CriterionDef criterion;
        for (int i = 0; i < criterionList.size(); i++) {
            criterion = criterionList.get(i);
            if (i == 0) {//先删除关联的标准再重新插入
                if (criterion.getExpandList() != null && !criterion.getExpandList().isEmpty()) {
                    criterion.setIsExpand(0);
                    //修改时先删除扩展表数据再重新插入
                    criterionExpandDao.deleteByCriterionId(criterion.getCriterionId(), userId);
                    criterionExpandDao.insertAll(criterion.getExpandList(), criterion.getCriterionId(), userId);
                } else {
                    criterion.setIsExpand(1);
                }
                SqlQueryMate sqlQueryMate = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
                sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                        .where(StringUtils.isNotBlank(criterion.getCriterionId()), "c_relation_id", ScriptConditionEnum.Eq, criterion.getCriterionId());
                CriterionDef relationCriterion = selectOne(sqlQueryMate, CriterionDef.class);
                if (relationCriterion != null) {
                    loopDelete(relationCriterion.getCriterionId(), userId);
                }
                if (criterion.getLayer() == 3) {
                    SqlQueryMate sqlQueryMate2 = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
                    sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                            .where(StringUtils.isNotBlank(criterion.getCriterionId()), "c_relation_id", ScriptConditionEnum.Eq, criterion.getCriterionId());
                    CriterionDef oldCriterion = selectOne(sqlQueryMate, CriterionDef.class);
                    if (!criterion.getIsPartakeEvaluation().equals(oldCriterion.getIsPartakeEvaluation())) {
                        //修改是否参与评价时，需要去维护评价记录表中的数量
                        String levelOneId = getLevelOneIdByLevelThreeId(criterion.getCriterionId());
                        criterionEvaluationDao.updateCountWhenPartakeChange(levelOneId, criterion.getIsPartakeEvaluation() == 0 ? 1 : -1);
                    }
                }
                update(user.userId, criterion);
            } else {
                criterion.setCriterionId(UUID.randomUUID().toString());
                criterion.setCreateId(user.userId);
                criterion.setCreateTime(LocalDateTime.now());
                criterion.setDbStatus(false);
                criterion.setRelationId(criterionList.get(i - 1).getCriterionId());
                if (criterion.getExpandList() != null && !criterion.getExpandList().isEmpty()) {
                    criterion.setIsExpand(0);
                    criterionExpandDao.insertAll(criterion.getExpandList(), criterion.getCriterionId(), userId);
                } else {
                    criterion.setIsExpand(1);
                }
                insert(user.userId, criterion);
            }
        }
    }

    private String getLevelOneIdByLevelThreeId(String criterionId) {
        String table = "t_criterion_def t1\n" +
                "                 join t_criterion_def t2 on t1.c_criterion_pid = t2.c_criterion_id and t2.c_db_status = 0";
        String columns = "t2.c_criterion_pid";
        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate.where(true, "t1.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(criterionId), "t1.c_criterion_id", ScriptConditionEnum.Eq, criterionId);
        return selectOne(sqlQueryMate, String.class);
    }

    @Override
    public CriterionDef getRelationById(String criterionId) {
        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
        sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
                .where(StringUtils.isNotBlank(criterionId), "c_criterion_id", ScriptConditionEnum.Eq, criterionId);
        return selectOne(sqlQueryMate, CriterionDef.class);
    }

    @Override
    public CriterionDef getOneById(String criterionId) {
//        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_criterion_def tcd").setColumns("tcd.*");
//        sqlQueryMate.where(true, "c_db_status", ScriptConditionEnum.Eq, 0)
//                .where(StringUtils.isNotBlank(criterionId), "c_criterion_id", ScriptConditionEnum.Eq, criterionId);
//        CriterionDef criterion = selectOne(sqlQueryMate, CriterionDef.class);
        CriterionDef criterion = getRelationById(criterionId);
        List<CriterionExpand> expandList = criterionExpandDao.getListByCriterionId(criterionId);
        criterion.setExpandList(expandList);
        if (StringUtils.isNotBlank(criterion.getExpression())) {
            List<CoreIndicatorsItem> expressionInfo = coreIndicatorsRuleDao.info(criterion.getExpression());
            criterion.setExpressionStr(expressionInfo.get(0).getName() + "/" + expressionInfo.get(1).getName());
        }
        return criterion;
    }

    @Override
    public List<Map<String, Object>> getCountList(String year) {
        return getMap(" SELECT result.criterionId,\n" +
                "               result.criterionName,\n" +
                "               result.zjEvaluationCount,\n" +
                "               result.zjReachCount,\n" +
                "               IFNULL(result.zjUnReachCount, result.total) AS zjUnReachCount,\n" +
                "               result.ckEvaluationCount,\n" +
                "               result.ckReachCount,\n" +
                "               IFNULL(result.ckUnReachCount, result.total) AS ckUnReachCount,\n" +
                "               result.xsEvaluationCount,\n" +
                "               result.xsReachCount,\n" +
                "               IFNULL(result.xsUnReachCount, result.total) AS xsUnReachCount,\n" +
                "               result.ptEvaluationCount,\n" +
                "               result.ptReachCount,\n" +
                "               IFNULL(result.ptUnReachCount, result.total) AS ptUnReachCount,\n" +
                "               result.mzEvaluationCount,\n" +
                "               result.mzReachCount,\n" +
                "               IFNULL(result.mzUnReachCount, result.total) AS mzUnReachCount,\n" +
                "               result.jkEvaluationCount,\n" +
                "               result.jkReachCount,\n" +
                "               IFNULL(result.jkUnReachCount, result.total) AS jkUnReachCount\n" +
                "        FROM (\n" +
                "                 SELECT def.c_criterion_id AS                                                     criterionId,\n" +
                "                        def.c_criterion_name AS                                                   criterionName,\n" +
                "                        max(count.counts) AS                                                      'zjEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '湛江市' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'zjReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '湛江市' THEN count.counts - evaluation.c_reach_count END) AS   'zjUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'ckEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '赤坎区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'ckReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '赤坎区' THEN count.counts - evaluation.c_reach_count END) AS   'ckUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'xsEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '霞山区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'xsReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '霞山区' THEN count.counts - evaluation.c_reach_count END) AS   'xsUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'ptEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '坡头区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'ptReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '坡头区' THEN count.counts - evaluation.c_reach_count END) AS   'ptUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'mzEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '麻章区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'mzReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '麻章区' THEN count.counts - evaluation.c_reach_count END) AS   'mzUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'jkEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '经济开发区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'jkReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '经济开发区' THEN count.counts - evaluation.c_reach_count END) AS 'jkUnReachCount',\n" +
                "                        max(count.counts)                                                         total\n" +
                "                 FROM t_criterion_def def\n" +
                "                          LEFT JOIN t_criterion_evaluation evaluation ON def.c_criterion_id = evaluation.c_criterion_id\n" +
                "                     AND evaluation.c_db_status = 0\n" +
                "                     AND evaluation.c_year ='"+year+"'" +
                "                          LEFT JOIN (\n" +
                "                     SELECT tt.levelOneId,\n" +
                "                            tt.levelOneName,\n" +
                "                            count(1) AS counts\n" +
                "                     FROM (\n" +
                "                              SELECT t1.c_criterion_id AS levelOneId,\n" +
                "                                     t1.c_criterion_name  levelOneName,\n" +
                "                                     t2.*\n" +
                "                              FROM t_criterion_def t1\n" +
                "                                       INNER JOIN t_criterion_def t2 ON t1.c_criterion_id = t2.c_criterion_pid\n" +
                "                                  AND t1.c_criterion_pid = '942940c6-2962-4951-a89d-f46c5124d553'\n" +
                "                                  AND t1.c_layer = 1\n" +
                "                                  AND t2.c_layer = 2\n" +
                "                                  AND t1.c_db_status = 0\n" +
                "                                  AND t2.c_db_status = 0\n" +
                "                          ) AS tt\n" +
                "                              INNER JOIN t_criterion_def t3 ON tt.c_criterion_id = t3.c_criterion_pid\n" +
                "                         AND t3.c_db_status = 0\n" +
                "                         AND t3.c_relation_id IS NULL\n" +
                "                         AND t3.c_is_partake_evaluation = 0\n" +
                "                     GROUP BY tt.levelOneId,\n" +
                "                              tt.levelOneName\n" +
                "                 ) count ON def.c_criterion_id = count.levelOneId\n" +
                "                 WHERE def.c_db_status = 0\n" +
                "                   AND def.c_layer = 1\n" +
                "                   AND def.c_criterion_pid = '942940c6-2962-4951-a89d-f46c5124d553'\n" +
                "                 GROUP BY def.c_criterion_id,\n" +
                "                          def.c_criterion_name\n" +
                "             ) result");
    }

    @Override
    public List<Map<String, Object>> getCountShowList(String year) {
        return getMap("SELECT result.criterionId,\n" +
                "               result.criterionName,\n" +
                "               result.zjEvaluationCount,\n" +
                "               result.zjReachCount,\n" +
                "               IFNULL(result.zjUnReachCount, result.total) AS zjUnReachCount,\n" +
                "               result.ckEvaluationCount,\n" +
                "               result.ckReachCount,\n" +
                "               IFNULL(result.ckUnReachCount, result.total) AS ckUnReachCount,\n" +
                "               result.xsEvaluationCount,\n" +
                "               result.xsReachCount,\n" +
                "               IFNULL(result.xsUnReachCount, result.total) AS xsUnReachCount,\n" +
                "               result.ptEvaluationCount,\n" +
                "               result.ptReachCount,\n" +
                "               IFNULL(result.ptUnReachCount, result.total) AS ptUnReachCount,\n" +
                "               result.mzEvaluationCount,\n" +
                "               result.mzReachCount,\n" +
                "               IFNULL(result.mzUnReachCount, result.total) AS mzUnReachCount,\n" +
                "               result.jkEvaluationCount,\n" +
                "               result.jkReachCount,\n" +
                "               IFNULL(result.jkUnReachCount, result.total) AS jkUnReachCount\n" +
                "        FROM (\n" +
                "                 SELECT def.c_criterion_id AS                                                     criterionId,\n" +
                "                        def.c_criterion_name AS                                                   criterionName,\n" +
                "                        max(count.counts) AS                                                      'zjEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '湛江市' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'zjReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '湛江市' THEN count.counts - evaluation.c_reach_count END) AS   'zjUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'ckEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '赤坎区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'ckReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '赤坎区' THEN count.counts - evaluation.c_reach_count END) AS   'ckUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'xsEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '霞山区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'xsReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '霞山区' THEN count.counts - evaluation.c_reach_count END) AS   'xsUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'ptEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '坡头区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'ptReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '坡头区' THEN count.counts - evaluation.c_reach_count END) AS   'ptUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'mzEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '麻章区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'mzReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '麻章区' THEN count.counts - evaluation.c_reach_count END) AS   'mzUnReachCount',\n" +
                "                        max(count.counts) AS                                                      'jkEvaluationCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '经济开发区' THEN evaluation.c_reach_count\n" +
                "                                ELSE 0 END) AS                                                    'jkReachCount',\n" +
                "                        max(CASE evaluation.c_map_name\n" +
                "                                WHEN '经济开发区' THEN count.counts - evaluation.c_reach_count END) AS 'jkUnReachCount',\n" +
                "                        max(count.counts)                                                         total\n" +
                "                 FROM t_criterion_def def\n" +
                "                          LEFT JOIN t_criterion_evaluation_show evaluation ON def.c_criterion_id = evaluation.c_criterion_id\n" +
                "                     AND evaluation.c_db_status = 0\n" +
                "                     AND evaluation.c_year ='"+year+"'" +
                "                          LEFT JOIN (\n" +
                "                     SELECT tt.levelOneId,\n" +
                "                            tt.levelOneName,\n" +
                "                            count(1) AS counts\n" +
                "                     FROM (\n" +
                "                              SELECT t1.c_criterion_id AS levelOneId,\n" +
                "                                     t1.c_criterion_name  levelOneName,\n" +
                "                                     t2.*\n" +
                "                              FROM t_criterion_def t1\n" +
                "                                       INNER JOIN t_criterion_def t2 ON t1.c_criterion_id = t2.c_criterion_pid\n" +
                "                                  AND t1.c_criterion_pid = '942940c6-2962-4951-a89d-f46c5124d553'\n" +
                "                                  AND t1.c_layer = 1\n" +
                "                                  AND t2.c_layer = 2\n" +
                "                                  AND t1.c_db_status = 0\n" +
                "                                  AND t2.c_db_status = 0\n" +
                "                          ) AS tt\n" +
                "                              INNER JOIN t_criterion_def t3 ON tt.c_criterion_id = t3.c_criterion_pid\n" +
                "                         AND t3.c_db_status = 0\n" +
                "                         AND t3.c_relation_id IS NULL\n" +
                "                         AND t3.c_is_partake_evaluation = 0\n" +
                "                     GROUP BY tt.levelOneId,\n" +
                "                              tt.levelOneName\n" +
                "                 ) count ON def.c_criterion_id = count.levelOneId\n" +
                "                 WHERE def.c_db_status = 0\n" +
                "                   AND def.c_layer = 1\n" +
                "                   AND def.c_criterion_pid = '942940c6-2962-4951-a89d-f46c5124d553'\n" +
                "                 GROUP BY def.c_criterion_id,\n" +
                "                          def.c_criterion_name\n" +
                "             ) result");
    }

    @Override
    public List<CriterionTableVO> getCriterionTableByMapIdAndYear(Map<String, String> map, Integer isReach, Integer isShow) {
        List<CriterionTableVO> list;
        if (isReach != null) {
            list = isShow == 0 ? getCriterionTableByMapIdAndYear(map) : getCriterionTableByMapIdAndYearShow(map);
            if (isReach == 0) {
                list = list.stream().filter(temp -> temp.getIsReach() != null && temp.getIsReach() == 0).collect(Collectors.toList());
            } else if (isReach == 1) {
                list = list.stream().filter(temp -> temp.getIsReach() == null || temp.getIsReach() != 0).collect(Collectors.toList());
            }
        } else {
            list = isShow == 0 ? getEvaluationTableByMapIdAndYear(map) : getEvaluationTableByMapIdAndYearShow(map);
        }
        List<CriterionTableVO> resultList = new ArrayList<>();
        Map<String, List<CriterionTableVO>> groupMap = list.stream().collect(Collectors.groupingBy(CriterionTableVO::getLevelTwoName));
        List<CriterionTableVO> groupList;
        CriterionTableVO tableVO;
        for (Map.Entry<String, List<CriterionTableVO>> e : groupMap.entrySet()) {
            groupList = e.getValue();
            if (groupList != null && !groupList.isEmpty()) {
                for (int i = 0; i < groupList.size(); i++) {
                    tableVO = new CriterionTableVO();
                    BeanUtils.copyProperties(groupList.get(i), tableVO);
                    if (i == 0) {
                        tableVO.setRowSpan(groupList.size());
                        tableVO.setColSpan(1);
                    } else {
                        tableVO.setRowSpan(0);
                        tableVO.setColSpan(0);
                    }
                    if (StringUtils.isNotBlank(tableVO.getExpressionCode())) {
                        List<CoreIndicatorsItem> expressionInfo = coreIndicatorsRuleDao.info(tableVO.getExpressionCode());
                        tableVO.setNumeratorStr(expressionInfo.get(0).getName());
                        tableVO.setDenominatorStr(expressionInfo.get(1).getName());
                        tableVO.setExpressionStr("(" + tableVO.getNumerator() + ")" + expressionInfo.get(0).getName() +
                                "/(" + tableVO.getDenominator() + ")" + expressionInfo.get(1).getName());
                    }
                    Boolean isShowReach = false;
                    if (groupList.get(i).getRelationType() != null) {
                        CriterionDef criterion = getRelationById(groupList.get(i).getLevelThreeId());
                        if (criterion != null) {
                            List<CriterionDef> criterionList = getById(groupList.get(i).getLevelThreeId());
                            StringBuilder realValueStr = new StringBuilder();
                            for (int j = 0; j < criterionList.size(); j++) {
                                List<CriterionEvaluation> evaluationList = criterionList.get(j).getEvaluationList().stream()
                                        .filter(a -> map.get("mapId").equals(a.getMapId()) && map.get("year").equals(a.getYear())).collect(Collectors.toList());
                                CriterionEvaluation evaluation;
                                if (!evaluationList.isEmpty()) {
                                    evaluation = evaluationList.get(0);
                                    tableVO.setIsReach(evaluation.getIsRealReach());
                                    //判断要求中含两个条件,是否都满足
                                    if (evaluation.getIsRealReach()>0){
                                        isShowReach=true;
                                    }
                                    if (j != criterionList.size() - 1) {
                                        realValueStr.append(evaluation.getRealValue() != null
                                                ? evaluation.getRealValue().toString()+ (tableVO.getUnit() == null ? "" : tableVO.getUnit()) + "," : "空,");
                                    } else {
                                        realValueStr.append(evaluation.getRealValue() != null
                                                ? evaluation.getRealValue().toString() + (tableVO.getUnit() == null ? "" : '%') : "空");
                                    }
                                }
                            }
                            tableVO.setRealValueStr(realValueStr.toString());
                            String delimiter;
                            if (criterionList.get(1).getRelationType() == 1) {
                                delimiter = "和";
                            } else {
                                delimiter = "或";
                            }
                            List<String> nameList = new ArrayList<>();
                            for (CriterionDef criterionDef : criterionList) {
                                StringBuilder name = new StringBuilder(criterionDef.getCriterionName());
                                if (criterionDef.getExpandList() != null && !criterionDef.getExpandList().isEmpty()) {
                                    name.append(criterionDef.getExpandList().get(0).getOperatorStr()).append(criterionDef.getExpandList().get(0).getValue())
                                            .append(StringUtils.isNotBlank(criterionDef.getExpandList().get(0).getUnit()) ?
                                                    criterionDef.getExpandList().get(0).getUnit() : "");
                                }
                                nameList.add(name.toString());
                            }
                            tableVO.setLevelThreeName(String.join(delimiter, nameList));
                        }
                    } else {
                        tableVO.setIsReach(groupList.get(i).getRealValue() != null ? groupList.get(i).getIsReach() : null);
                    }
                    if (isShowReach) {
                        tableVO.setIsReach(1);
                    }
                    resultList.add(tableVO);
                }
            }
        }
        resultList = resultList.stream()
                .sorted(Comparator.comparing(CriterionTableVO::getLevelTwoOrder))
                .collect(Collectors.toList());
        return resultList;
    }

    private List<CriterionTableVO> getEvaluationTableByMapIdAndYearShow(Map<String, String> map) {
        String sql = "SELECT\n" +
                "            tt.levelOneName,\n" +
                "            tt.c_criterion_name AS levelTwoName,\n" +
                "            tt.c_order_num levelTwoOrder,\n" +
                "            t3.c_criterion_id AS levelThreeId,\n" +
                "            t3.c_order_num levelThreeOrder,\n" +
                "            CONCAT(t3.c_criterion_name,IFNULL(dict.c_dict_name,''),IFNULL(expand.c_value,''),IFNULL(expand.c_unit,'')) AS levelThreeName,\n" +
                "            t3.c_is_expand AS isExpand,\n" +
                "            evaluation.c_is_real_reach as isReach,\n" +
                "            evaluation.c_real_value AS realValue,\n" +
                "            relation.c_relation_type as relationType,\n" +
                "               expand.c_unit as unit\n" +
                "        FROM\n" +
                "            (\n" +
                "                SELECT\n" +
                "                    t1.c_criterion_id AS levelOneId,\n" +
                "                    t1.c_criterion_name levelOneName,\n" +
                "                    t2.*\n" +
                "                FROM t_criterion_def t1\n" +
                "                         INNER JOIN t_criterion_def t2 ON t1.c_criterion_id = t2.c_criterion_pid\n" +
                "                    AND t1.c_criterion_id = :criterionId\n" +
                "                    AND t1.c_layer = 1\n" +
                "                    AND t2.c_layer = 2\n" +
                "                    AND t1.c_db_status = 0\n" +
                "                    AND t2.c_db_status = 0\n" +
                "            ) AS tt\n" +
                "                INNER JOIN t_criterion_def t3 ON tt.c_criterion_id = t3.c_criterion_pid and t3.c_relation_id is null and\n" +
                "                                                 t3.c_is_partake_evaluation = 0\n" +
                "                AND t3.c_db_status = 0\n" +
                "                left JOIN t_criterion_evaluation_show evaluation ON t3.c_criterion_id = evaluation.c_criterion_id\n" +
                "                AND evaluation.c_db_status = 0\n" +
                "                AND evaluation.c_map_id = :mapId\n" +
                "                AND evaluation.c_year = :year\n" +
                "                left join t_criterion_def relation\n" +
                "                          on t3.c_criterion_id = relation.c_relation_id and relation.c_db_status = 0\n" +
                "                left join t_criterion_expand expand\n" +
                "                          on expand.c_criterion_id = t3.c_criterion_id and expand.c_db_status = 0 and expand.c_level = 1\n" +
                "                left join t_sys_dict dict on expand.c_operator = dict.c_dict_code and dict.c_db_status = 0 and\n" +
                "                                             dict.c_ns_id = 'eefa4ef3-b23b-4b91-aece-fc9bb38cdd95'";
        Value<List<CriterionTableVO>> result = new Value<>();
        run(sql, CommandType.Text, dbCommand -> {
            dbCommand.addInParameter("criterionId", map.get("criterionId"));
            dbCommand.addInParameter("mapId", map.get("mapId"));
            dbCommand.addInParameter("year", map.get("year"));
            result.data = dbCommand.executeToList(CriterionTableVO.class);
        });
        return result.data;
    }

    private List<CriterionTableVO> getEvaluationTableByMapIdAndYear(Map<String, String> map) {
        String sql = "SELECT\n" +
                "            tt.levelOneName,\n" +
                "            tt.c_criterion_name AS levelTwoName,\n" +
                "            tt.c_order_num levelTwoOrder,\n" +
                "            t3.c_criterion_id AS levelThreeId,\n" +
                "            t3.c_order_num levelThreeOrder,\n" +
                "            CONCAT(t3.c_criterion_name,IFNULL(dict.c_dict_name,''),IFNULL(expand.c_value,''),IFNULL(expand.c_unit,'')) AS levelThreeName,\n" +
                "            t3.c_is_expand AS isExpand,\n" +
                "            evaluation.c_is_real_reach as isReach,\n" +
                "            evaluation.c_real_value AS realValue,\n" +
                "            relation.c_relation_type as relationType,\n" +
                "               expand.c_unit as unit\n" +
                "        FROM\n" +
                "            (\n" +
                "                SELECT\n" +
                "                    t1.c_criterion_id AS levelOneId,\n" +
                "                    t1.c_criterion_name levelOneName,\n" +
                "                    t2.*\n" +
                "                FROM t_criterion_def t1\n" +
                "                         INNER JOIN t_criterion_def t2 ON t1.c_criterion_id = t2.c_criterion_pid\n" +
                "                    AND t1.c_criterion_id = :criterionId\n" +
                "                    AND t1.c_layer = 1\n" +
                "                    AND t2.c_layer = 2\n" +
                "                    AND t1.c_db_status = 0\n" +
                "                    AND t2.c_db_status = 0\n" +
                "            ) AS tt\n" +
                "                INNER JOIN t_criterion_def t3 ON tt.c_criterion_id = t3.c_criterion_pid and t3.c_relation_id is null and\n" +
                "                                                 t3.c_is_partake_evaluation = 0\n" +
                "                AND t3.c_db_status = 0\n" +
                "                left JOIN t_criterion_evaluation evaluation ON t3.c_criterion_id = evaluation.c_criterion_id\n" +
                "                AND evaluation.c_db_status = 0\n" +
                "                AND evaluation.c_map_id = :mapId\n" +
                "                AND evaluation.c_year = :year\n" +
                "                left join t_criterion_def relation\n" +
                "                          on t3.c_criterion_id = relation.c_relation_id and relation.c_db_status = 0\n" +
                "                left join t_criterion_expand expand\n" +
                "                          on expand.c_criterion_id = t3.c_criterion_id and expand.c_db_status = 0 and expand.c_level = 1\n" +
                "                left join t_sys_dict dict on expand.c_operator = dict.c_dict_code and dict.c_db_status = 0 and\n" +
                "                                             dict.c_ns_id = 'eefa4ef3-b23b-4b91-aece-fc9bb38cdd95'";
        Value<List<CriterionTableVO>> result = new Value<>();
        run(sql, CommandType.Text, dbCommand -> {
            dbCommand.addInParameter("criterionId", map.get("criterionId"));
            dbCommand.addInParameter("mapId", map.get("mapId"));
            dbCommand.addInParameter("year", map.get("year"));
            result.data = dbCommand.executeToList(CriterionTableVO.class);
        });
        return result.data;
    }

    private List<CriterionTableVO> getCriterionTableByMapIdAndYearShow(Map<String, String> map) {
        String sql = "SELECT tt.levelOneName,\n" +
                "               tt.c_criterion_name               AS levelTwoName,\n" +
                "               tt.c_order_num                       levelTwoOrder,\n" +
                "               t3.c_criterion_id                 AS levelThreeId,\n" +
                "               t3.c_order_num                       levelThreeOrder,\n" +
                "               CONCAT(t3.c_criterion_name, IFNULL(dict.c_dict_name, ''), IFNULL(expand.c_value, ''),\n" +
                "                      IFNULL(expand.c_unit, '')) AS levelThreeName,\n" +
                "               t3.c_is_expand                    AS isExpand,\n" +
                "               t3.c_expression as expressionCode,\n" +
                "\t\t\t   evaluation.c_numerator numerator,\n" +
                "\t\t\t   evaluation.c_denominator denominator,\n" +
                "               evaluation.c_real_value           AS realValue,\n" +
                "               evaluation.c_is_real_reach        AS isReach,\n" +
                "               relation.c_relation_type          as relationType,\n" +
                "               expand.c_unit as unit\n" +
                "        FROM (\n" +
                "                 SELECT t1.c_criterion_id AS levelOneId,\n" +
                "                        t1.c_criterion_name  levelOneName,\n" +
                "                        t2.*\n" +
                "                 FROM t_criterion_def t1\n" +
                "                          INNER JOIN t_criterion_def t2 ON t1.c_criterion_id = t2.c_criterion_pid\n" +
                "                     AND t1.c_criterion_id = :criterionId\n" +
                "                     AND t1.c_layer = 1\n" +
                "                     AND t2.c_layer = 2\n" +
                "                     AND t1.c_db_status = 0\n" +
                "                     AND t2.c_db_status = 0\n" +
                "             ) AS tt\n" +
                "                 INNER JOIN t_criterion_def t3\n" +
                "                            ON tt.c_criterion_id = t3.c_criterion_pid and t3.c_relation_id is null and\n" +
                "                               t3.c_is_partake_evaluation = 0\n" +
                "                                AND t3.c_db_status = 0\n" +
                "                 left JOIN t_criterion_evaluation_show evaluation ON t3.c_criterion_id = evaluation.c_criterion_id\n" +
                "            AND evaluation.c_db_status = 0\n" +
                "            AND evaluation.c_map_id = :mapId\n" +
                "            AND evaluation.c_year = :year\n" +
                "                 left join t_criterion_def relation\n" +
                "                           on t3.c_criterion_id = relation.c_relation_id and relation.c_db_status = 0\n" +
                "                 left join t_criterion_expand expand\n" +
                "                           on expand.c_criterion_id = t3.c_criterion_id and expand.c_db_status = 0 and\n" +
                "                              expand.c_level = 1\n" +
                "                 left join t_sys_dict dict on expand.c_operator = dict.c_dict_code and dict.c_db_status = 0 and\n" +
                "                                              dict.c_ns_id = 'eefa4ef3-b23b-4b91-aece-fc9bb38cdd95'";
        Value<List<CriterionTableVO>> result = new Value<>();
        run(sql, CommandType.Text, dbCommand -> {
            dbCommand.addInParameter("criterionId", map.get("criterionId"));
            dbCommand.addInParameter("mapId", map.get("mapId"));
            dbCommand.addInParameter("year", map.get("year"));
            result.data = dbCommand.executeToList(CriterionTableVO.class);
        });
        return result.data;
    }

    private List<CriterionTableVO> getCriterionTableByMapIdAndYear(Map<String, String> map) {
        String sql = "SELECT tt.levelOneName,\n" +
                "               tt.c_criterion_name               AS levelTwoName,\n" +
                "               tt.c_order_num                       levelTwoOrder,\n" +
                "               t3.c_criterion_id                 AS levelThreeId,\n" +
                "               t3.c_order_num                       levelThreeOrder,\n" +
                "               CONCAT(t3.c_criterion_name, IFNULL(dict.c_dict_name, ''), IFNULL(expand.c_value, ''),\n" +
                "                      IFNULL(expand.c_unit, '')) AS levelThreeName,\n" +
                "               t3.c_is_expand                    AS isExpand,\n" +
                "               t3.c_expression as expressionCode,\n" +
                "\t\t\t   evaluation.c_numerator numerator,\n" +
                "\t\t\t   evaluation.c_denominator denominator,\n" +
                "               evaluation.c_real_value           AS realValue,\n" +
                "               evaluation.c_is_real_reach        AS isReach,\n" +
                "               relation.c_relation_type          as relationType,\n" +
                "               expand.c_unit as unit\n" +
                "        FROM (\n" +
                "                 SELECT t1.c_criterion_id AS levelOneId,\n" +
                "                        t1.c_criterion_name  levelOneName,\n" +
                "                        t2.*\n" +
                "                 FROM t_criterion_def t1\n" +
                "                          INNER JOIN t_criterion_def t2 ON t1.c_criterion_id = t2.c_criterion_pid\n" +
                "                     AND t1.c_criterion_id = :criterionId\n" +
                "                     AND t1.c_layer = 1\n" +
                "                     AND t2.c_layer = 2\n" +
                "                     AND t1.c_db_status = 0\n" +
                "                     AND t2.c_db_status = 0\n" +
                "             ) AS tt\n" +
                "                 INNER JOIN t_criterion_def t3\n" +
                "                            ON tt.c_criterion_id = t3.c_criterion_pid and t3.c_relation_id is null and\n" +
                "                               t3.c_is_partake_evaluation = 0\n" +
                "                                AND t3.c_db_status = 0\n" +
                "                 left JOIN t_criterion_evaluation evaluation ON t3.c_criterion_id = evaluation.c_criterion_id\n" +
                "            AND evaluation.c_db_status = 0\n" +
                "            AND evaluation.c_map_id = :mapId\n" +
                "            AND evaluation.c_year = :year\n" +
                "                 left join t_criterion_def relation\n" +
                "                           on t3.c_criterion_id = relation.c_relation_id and relation.c_db_status = 0\n" +
                "                 left join t_criterion_expand expand\n" +
                "                           on expand.c_criterion_id = t3.c_criterion_id and expand.c_db_status = 0 and\n" +
                "                              expand.c_level = 1\n" +
                "                 left join t_sys_dict dict on expand.c_operator = dict.c_dict_code and dict.c_db_status = 0 and\n" +
                "                                              dict.c_ns_id = 'eefa4ef3-b23b-4b91-aece-fc9bb38cdd95'";
        Value<List<CriterionTableVO>> result = new Value<>();
        run(sql, CommandType.Text, dbCommand -> {
            dbCommand.addInParameter("criterionId", map.get("criterionId"));
            dbCommand.addInParameter("mapId", map.get("mapId"));
            dbCommand.addInParameter("year", map.get("year"));
            result.data = dbCommand.executeToList(CriterionTableVO.class);
        });
        return result.data;
    }

    @Override
    public List<Map<String, Object>> getHomeInfo(String mapId) {
        String table = "t_criterion_def t\n" +
                "       left JOIN t_criterion_evaluation t2 ON t.c_criterion_id = t2.c_criterion_id";
        String columns = "t.c_criterion_name as criterionName,\n" +
                "         IFNULL(t2.c_numerator, 0) as numerator,\n" +
                "         IFNULL(t2.c_denominator, 0) as denominator,\n" +
                "         IFNULL(t2.c_real_value, 0) realValue";
        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate.where(true, "t.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "t2.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "t2.c_year", ScriptConditionEnum.Eq, LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy")))
                .where(true, "t.c_criterion_id", ScriptConditionEnum.In, "('7160bab4-d7bc-4526-86ac-0312bd6bc402', 'b5ba9cf9-94a6-4807-b590-2a4e0fd60ab9',\n" +
                        "                                     '2cba7c6b-dea5-484e-92bf-bd67b373add4', '37f085dc-b8e6-452b-a5db-b048bcff0560')");
        return getMap(sqlQueryMate);
    }

    @Override
    public List<Map<String, Object>> getHomeInfoShow(String mapId) {
      return   getMap("SELECT t.c_criterion_name as criterionName,\n" +
                "               IFNULL(t2.c_numerator, 0) as numerator,\n" +
                "               IFNULL(t2.c_denominator, 0) as denominator,\n" +
                "               IFNULL(t2.c_real_value, 0) realValue\n" +
                "        FROM t_criterion_def t\n" +
                "                 left JOIN t_criterion_evaluation_show t2 ON t.c_criterion_id = t2.c_criterion_id\n" +
                "            AND t.c_db_status = 0\n" +
                "            AND t2.c_db_status = 0\n" +
                "            AND t2.c_year = DATE_FORMAT(NOW(), '%Y')\n" +
                "            AND t2.c_map_id = '\n"+mapId +
                "'            where t.c_criterion_id IN ('7160bab4-d7bc-4526-86ac-0312bd6bc402', 'b5ba9cf9-94a6-4807-b590-2a4e0fd60ab9',\n" +
                "                                     '2cba7c6b-dea5-484e-92bf-bd67b373add4', '37f085dc-b8e6-452b-a5db-b048bcff0560')");
    }
}
